cap program drop xlstocsv
program define xlstocsv

 version 16
 
 syntax [anything], [todir(string) FIRST]
	local ossys=c(os) 
	if "`ossys'"!= "Windows"{
		di as red "Error: xlstocsv only used in Windows."
		exit
	}

	local anything `anything'
	if `"`anything'"'==""{
		db xlstocsv
		exit
	}
	else{
		tempname flag dirflag
		mata: analyzea(`"`anything'"',"`flag'")
		
		if `flag'==-1{
			di as red `"Error: file [`anything'] not found"'
			exit
		}
		if `flag'==-2{
			di as red `"Error: Directory [`anything'] not exist"'
			exit
		}
		
	}
	//local first `first'
	//disp "`first'"
	if `"`todir'"'!=""{
		cap mkdir `"`todir'"'
		mata: st_numscalar("`dirflag'", direxists(`"`todir'"'))
		if `dirflag'==0{
		    di as red "ERROR:" `"`todir'"' "not exist."
			exit
		}
	
	}
	else{
        local anything2 `anything'
		if `flag'==1{
			mata: st_local("anything2",pathgetparent(`"`anything'"')) 
			mata: st_local("filename2",pathbasename(`"`anything'"')) 
		}
		cap mkdir `"`anything2'/CSV"'
		local todir `"`anything2'/CSV"'
	}

	if `flag'==1{
		cap erase "`pwd'\_xls_to_csv_.vbs"
		mata:writevbsfile`first'(`"`anything'"',`"`filename2'"',`"`todir'"')
	}
	
	if `flag'==2{
		cap erase "`pwd'\_xls_to_csv_.vbs"
		mata:writevbs`first'(`"`anything'"',`"`todir'"')
	}
	
	di _n `"csv files are stored in `todir'"'

	local pwd=c(pwd)
	!"`pwd'\_xls_to_csv_.vbs"
	cap erase "`pwd'\_xls_to_csv_.vbs"
    
 end
 
 
 
 cap mata mata drop analyzea()
 cap mata mata drop writevbs()
 cap mata mata drop writevbsfile()
 cap mata mata drop writevbsfirst()
 cap mata mata drop writevbsfilefirst()
mata:

void function analyzea(string scalar path,string scalar flag)
{
	path2= pathbasename(path)
	if( usubstr(path2,-4,4)==".xls"|usubstr(path2,-5,5)==".xlsx"){
		xflag=1
		if(!fileexists(path)){
			xflag=-1
			 if( direxists(path)) {
				xflag=2
			 }				
		}
	}
	else{
		 if( direxists(path)) {
		 	xflag=2
		 }
		 else{
		 	xflag=-2
		 }
	}
	
	st_numscalar(flag,xflag)
	
	
}


void function writevbs(string scalar path,string scalar todir)
{
	
	cc=`"""'
	writefile=fopen("_xls_to_csv_.vbs","rw")
	fwrite(writefile, sprintf(`"Set objFSO = CreateObject("Scripting.FileSystemObject")  \n"'))
	fwrite(writefile, sprintf(`"Set objExcel = CreateObject("Excel.Application")  \n"'))
	fwrite(writefile, sprintf(`"objExcel.DisplayAlerts = False  \n"'))
	fwrite(writefile, sprintf(`"strFolder = %s%s\%s  \n"',cc,path,cc))
	fwrite(writefile, sprintf(`"strFolder2 =%s%s\%s  \n"',cc,todir,cc))
	fwrite(writefile, sprintf(`"Set objFiles = objFSO.GetFolder(strFolder).Files  \n"'))
	fwrite(writefile, sprintf(`"For Each objFile In objFiles  \n"'))
	fwrite(writefile, sprintf(`"    If Lcase(Split(objFile.Name, ".")(1)) = "xls" Or Lcase(Split(objFile.Name, ".")(1)) = "xlsx" Then  \n"'))
	fwrite(writefile, sprintf(`"       Set objWorkbook = objExcel.Workbooks.Open(strFolder & objFile.Name) \n"'))
	fwrite(writefile, sprintf(`"       For Each sheet In objWorkBook.Sheets \n"'))
	fwrite(writefile, sprintf(`"         objWorkbook.sheets(sheet.Name).Activate  \n"')) 
	fwrite(writefile, sprintf(`"         If objExcel.Application.WorksheetFunction.CountA(sheet.Cells) <> 0 Then \n"'))
	//fwrite(writefile, sprintf(`"          Set objWorksheet = objWorkbook.Worksheets(sheet)  \n"'))
	//fwrite(writefile, sprintf(`"          objWorksheet.SaveAs strFolder2 & Split(objFile.Name, ".")(0) & "_" & sheet.Name & ".csv", 6  \n"'))
	fwrite(writefile, sprintf(`"          objWorkbook.SaveAs strFolder2 & Split(objFile.Name, ".")(0) & "_" & sheet.Name & ".csv", 6  \n"'))
	fwrite(writefile, sprintf(`"         End If  \n"'))
	fwrite(writefile, sprintf(`"       Next  \n"'))
	fwrite(writefile, sprintf(`"       objWorkbook.Close  \n"'))
	fwrite(writefile, sprintf(`"    End If  \n"'))
    fwrite(writefile, sprintf(`"Next  \n"'))
    fwrite(writefile, sprintf(`"objExcel.Quit  \n"'))
	fclose(writefile)
}

void function writevbsfile(string scalar filename,string scalar filename2,string scalar todir)
{
	cc=`"""'	
	writefile=fopen("_xls_to_csv_.vbs","rw")
	fwrite(writefile, sprintf(`"Set objFSO = CreateObject("Scripting.FileSystemObject")  \n"'))
	fwrite(writefile, sprintf(`"Set objExcel = CreateObject("Excel.Application")  \n"'))
	fwrite(writefile, sprintf(`"objExcel.DisplayAlerts = False  \n"'))
	fwrite(writefile, sprintf(`"strFolder = %s%s%s  \n"',cc,filename,cc))
	fwrite(writefile, sprintf(`"strFolder2 =%s%s\%s  \n"',cc,todir,cc))
	fwrite(writefile, sprintf(`"filename2 = %s%s%s  \n"',cc,filename2,cc))
	fwrite(writefile, sprintf(`"Set objWorkbook = objExcel.Workbooks.Open(strFolder) \n"'))
	fwrite(writefile, sprintf(`"   For Each sheet In objWorkBook.Sheets \n"'))
	fwrite(writefile, sprintf(`"      objWorkbook.sheets(sheet.Name).Activate  \n"')) 
	fwrite(writefile, sprintf(`"      If objExcel.Application.WorksheetFunction.CountA(sheet.Cells) <> 0 Then \n"'))
    //fwrite(writefile, sprintf(`"        Set objWorksheet = objWorkbook.Worksheets(sheet)  \n"'))
    //fwrite(writefile, sprintf(`"        objWorksheet.SaveAs strFolder2 & Split(filename2, ".")(0) & "_" & sheet.Name & ".csv", 6  \n"'))    
	fwrite(writefile, sprintf(`"        objWorkbook.SaveAs strFolder2 & Split(filename2, ".")(0) & "_" & sheet.Name & ".csv", 6  \n"'))
	fwrite(writefile, sprintf(`"      End If  \n"'))
	fwrite(writefile, sprintf(`"    Next  \n"'))
	fwrite(writefile, sprintf(`"objWorkbook.Close  \n"'))
    fwrite(writefile, sprintf(`"objExcel.Quit  \n"'))
	fclose(writefile)
}



void function writevbsfirst(string scalar path,string scalar todir)
{
	
	cc=`"""'
	writefile=fopen("_xls_to_csv_.vbs","rw")
	fwrite(writefile, sprintf(`"Set objFSO = CreateObject("Scripting.FileSystemObject")  \n"'))
	fwrite(writefile, sprintf(`"Set objExcel = CreateObject("Excel.Application")  \n"'))
	fwrite(writefile, sprintf(`"objExcel.DisplayAlerts = False  \n"'))
	fwrite(writefile, sprintf(`"strFolder = %s%s\%s  \n"',cc,path,cc))
	fwrite(writefile, sprintf(`"strFolder2 =%s%s\%s  \n"',cc,todir,cc))
	fwrite(writefile, sprintf(`"Set objFiles = objFSO.GetFolder(strFolder).Files  \n"'))
	fwrite(writefile, sprintf(`"For Each objFile In objFiles  \n"'))
	fwrite(writefile, sprintf(`"    If Lcase(Split(objFile.Name, ".")(1)) = "xls" Or Lcase(Split(objFile.Name, ".")(1)) = "xlsx" Then  \n"'))
	fwrite(writefile, sprintf(`"       Set objWorkbook = objExcel.Workbooks.Open(strFolder & objFile.Name) \n"'))
	fwrite(writefile, sprintf(`"       objWorkbook.SaveAs strFolder2 & Split(objFile.Name, ".")(0) & ".csv", 6  \n"'))
	fwrite(writefile, sprintf(`"       objWorkbook.Close  \n"'))
	fwrite(writefile, sprintf(`"    End If  \n"'))
    fwrite(writefile, sprintf(`"Next  \n"'))
    fwrite(writefile, sprintf(`"objExcel.Quit  \n"'))
	fclose(writefile)
}

void function writevbsfilefirst(string scalar filename,string scalar filename2,string scalar todir)
{
	cc=`"""'	
	writefile=fopen("_xls_to_csv_.vbs","rw")
	fwrite(writefile, sprintf(`"Set objFSO = CreateObject("Scripting.FileSystemObject")  \n"'))
	fwrite(writefile, sprintf(`"Set objExcel = CreateObject("Excel.Application")  \n"'))
	fwrite(writefile, sprintf(`"objExcel.DisplayAlerts = False  \n"'))
	fwrite(writefile, sprintf(`"strFolder = %s%s%s  \n"',cc,filename,cc))
	fwrite(writefile, sprintf(`"strFolder2 =%s%s\%s  \n"',cc,todir,cc))
	fwrite(writefile, sprintf(`"filename2 = %s%s%s  \n"',cc,filename2,cc))
	fwrite(writefile, sprintf(`"Set objWorkbook = objExcel.Workbooks.Open(strFolder) \n"'))
	fwrite(writefile, sprintf(`"objWorkbook.SaveAs strFolder2 & Split(filename2, ".")(0) & ".csv", 6  \n"'))
	fwrite(writefile, sprintf(`"objWorkbook.Close  \n"'))
    fwrite(writefile, sprintf(`"objExcel.Quit  \n"'))
	fclose(writefile)
}




end
